----Xe
if object_id('sp_TraCuuXe', 'p') is not null
drop proc sp_TraCuuXe
go
create proc sp_TraCuuXe
        @maxe int, @bangsoxe nvarchar(50), @soluongghe int, @trangthai int, @maloaixe int, @trongluongtaihang float
as
begin
		declare @query nvarchar(1000), @flag int
		set @query = 'select * from xe'
		set @flag = 0
		
		if(@maxe<>0)
			begin
				set @query = @query + ' where maxe = ' + @maxe
				set @flag = 1
			end
		
		if(@bangsoxe<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and bangsoxe like ' + N'''%' + @bangsoxe + '%'''
				else
					begin
						set @query = @query + ' where bangsoxe like ' + N'''%' + @bangsoxe + '%'''
						set @flag = 1
					end
			end
			
		if(@soluongghe<>0)
			begin
				if(@flag = 1)
					set @query = @query + ' and soluongghe = ' + @soluongghe
				else
					begin
						set @query = @query + ' where soluongghe = ' + @soluongghe
						set @flag = 1
					end
			end
		
		if(@trangthai<>0)
			begin
				if(@flag = 1)
					set @query = @query + ' and trangthai = ' + @trangthai
				else
					begin
						set @query = @query + ' where trangthai = ' + @trangthai
						set @flag = 1
					end
			end
		
		if(@maloaixe<>0)
			begin
				if(@flag = 1)
					set @query = @query + ' and maloaixe = ' + @maloaixe
				else
					begin
						set @query = @query + ' where maloaixe = ' + @maloaixe
						set @flag = 1
					end
			end
			
		if(@trongluongtaihang<>0)
			begin
				if(@flag = 1)
					set @query = @query + ' and trongluongtaihang = ' + @trongluongtaihang
				else
					begin
						set @query = @query + ' where trongluongtaihang = ' + @trongluongtaihang
						set @flag = 1
					end
			end
		exec (@query)
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuXe '', '', '', '', '', ''
----
if object_id('sp_TraCuuXeTheoMaDatVe', 'p') is not null
drop proc sp_TraCuuXeTheoMaDatVe
go
create proc sp_TraCuuXeTheoMaDatVe
	@madatve int
as
begin
		select xe.* from xe, datve
		where datve.madatve = @madatve
		and xe.maxe = datve.maxe
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuXeTheoMaDatVe 2
----
if object_id('sp_TraCuuSoLuongGheTheoMaXe', 'p') is not null
drop proc sp_TraCuuSoLuongGheTheoMaXe
go
create proc sp_TraCuuSoLuongGheTheoMaXe
	@maxe int
as
begin
		select soluongghe from xe
		where maxe = @maxe
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuSoLuongGheTheoMaXe 1
----
if object_id('sp_ThemXe', 'p') is not null
		drop proc sp_ThemXe
		go
		create proc sp_ThemXe
			@maxe int, @bangsoxe nvarchar(10), @action int, @soluongghethem int, 
			@soluongghe int, @trangthai int, @maloaixe int, @trongluongtaihang int
		as
		begin tran
		--set tran isolation level read committed --giải quyết tranh chấp
			if (@maxe = 0)
				insert into xe(bangsoxe,soluongghe,trangthai,maloaixe,trongluongtaihang)
				values (@bangsoxe, @soluongghe, @trangthai, @maloaixe, @trongluongtaihang)
			else
				if(@action = 0)
					begin
						update xe
						set bangsoxe = @bangsoxe,
							soluongghe = @soluongghe,
							trangthai = @trangthai,
							maloaixe = @maloaixe,
							trongluongtaihang = @trongluongtaihang
							where maxe = @maxe
					end
				else
					begin
						declare @soluong int
						set @soluong = (select soluongghe from xe
											where maxe = @maxe)
						set @soluong = @soluong + @soluongghethem
						update xe
						set bangsoxe = @bangsoxe,
							soluongghe = @soluong,
							trangthai = @trangthai,
							maloaixe = @maloaixe,
							trongluongtaihang = @trongluongtaihang
							where maxe = @maxe
					end
				if(@@error <> 0)begin
						rollback tran
						return
				end
		commit
exec sp_ThemXe '10','8988', 1, 50, 1, 2, 10

----LoaiXe
if object_id('sp_TraCuuLoaiXe', 'p') is not null
drop proc sp_TraCuuLoaiXe
go
create proc sp_TraCuuLoaiXe
        @maloaixe nvarchar(50), @tenloaixe nvarchar(50), @mota nvarchar(max)
as
begin
		declare @query nvarchar(1000), @flag int
		set @query = 'select * from loaixe'
		set @flag = 0
		
		if(@maloaixe<>'')
			begin
				set @query = @query + ' where maloaixe = ' + @maloaixe
				set @flag = 1
			end
		
		if(@tenloaixe<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and tenloaixe like ' + 'N''%' + @tenloaixe + '%'''
				else
					begin
						set @query = @query + ' where tenloaixe like ' + 'N''%' + @tenloaixe + '%'''
						set @flag = 1
					end
			end
			
		if(@mota<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and mota like ' + N'''%' + @mota + '%'''
				else
					begin
						set @query = @query + ' where mota like ' + N'''%' + @mota + '%'''
						set @flag = 1
					end
			end
		print(@query)
		exec (@query)
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuLoaiXe '1','', ''

if object_id('sp_ThemLoaiXe', 'p') is not null
drop proc sp_ThemLoaiXe
go
create proc sp_ThemLoaiXe
@maloaixe int, @tenloaixe nvarchar(50), @mota nvarchar(max)
as
begin
	if (@maloaixe = 0)
        insert into loaixe(tenloaixe, mota)
        values(@tenloaixe, @mota)
    else
		update loaixe
		set	tenloaixe = @tenloaixe,
			mota = @mota
		where maloaixe = @maloaixe
    if(@@error <> 0)begin
                rollback tran
                return
    end
end

exec sp_ThemLoaiXe '', N'Loại x', N'Loại X'

if object_id('sp_XoaXe', 'p') is not null
drop proc sp_XoaXe
go
create proc sp_XoaXe
        @maxe int
as
begin
        delete from xe
        where maxe = @maxe
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_XoaXe 1

if object_id('sp_XoaLoaiXe', 'p') is not null
drop proc sp_XoaLoaiXe
go
create proc sp_XoaLoaiXe
        @maloaixe int
as
begin
        delete from loaixe
        where maloaixe = @maloaixe
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_XoaLoaiXe 3

if object_id('sp_TraCuuPhanQuyen', 'p') is not null
drop proc sp_TraCuuPhanQuyen
go
create proc sp_TraCuuPhanQuyen
    @maphanquyen nvarchar(50), @capnhattaikhoan nvarchar(50), @dangnhap nvarchar(50), @dangxuat nvarchar(50), @tracuu nvarchar(50), @thaotacdatve nvarchar(50),
    @capnhatthongtinve nvarchar(50), @thaotachuyve nvarchar(50), @dieuphoichuyenxe nvarchar(50), @quanlydaily nvarchar(50), @quanlydatve nvarchar(50),
    @quanlykhachhang nvarchar(50), @tinhchiphi nvarchar(50), @thongkenhanvien nvarchar(50), @thongkedoanhthu nvarchar(50), @baocaodoanhthu nvarchar(50),
    @quanlyhoadon nvarchar(50), @quanlykhohang nvarchar(50), @quanlylichtrinh nvarchar(50), @quanlynguoidung nvarchar(50), @quanlynhanvien nvarchar(50),
    @quanlytuyenxe nvarchar(50), @quanlyxe nvarchar(50)
as
begin
		declare @query nvarchar(1000), @flag int
		set @query = 'select * from phanquyen'
		set @flag = 0
		
		if(@maphanquyen<>'')
			begin
				set @query = @query + ' where maphanquyen = ' + @maphanquyen
				set @flag = 1
			end
		
		if(@capnhattaikhoan<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and capnhattaikhoan = ' + @capnhattaikhoan
				else
					begin
						set @query = @query + ' where capnhattaikhoan = ' + @capnhattaikhoan
						set @flag = 1
					end
			end
			
		if(@dangnhap<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and dangnhap = ' + @dangnhap
				else
					begin
						set @query = @query + ' where dangnhap = ' + @dangnhap
						set @flag = 1
					end
			end
		
		if(@dangxuat<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and dangxuat = ' + @dangxuat
				else
					begin
						set @query = @query + ' where dangxuat = ' + @dangxuat
						set @flag = 1
					end
			end
		
		if(@tracuu<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and tracuu = ' + @tracuu
				else
					begin
						set @query = @query + ' where tracuu = ' + @tracuu
						set @flag = 1
					end
			end
			
		if(@thaotacdatve<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and thaotacdatve = ' + @thaotacdatve
				else
					begin
						set @query = @query + ' where thaotacdatve = ' + @thaotacdatve
						set @flag = 1
					end
			end
		
		if(@capnhatthongtinve<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and capnhatthongtinve = ' + @capnhatthongtinve
				else
					begin
						set @query = @query + ' where capnhatthongtinve = ' + @capnhatthongtinve
						set @flag = 1
					end
			end
			
		if(@thaotachuyve<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and thaotachuyve = ' + @thaotachuyve
				else
					begin
						set @query = @query + ' where thaotachuyve = ' + @thaotachuyve
						set @flag = 1
					end
			end
		
		if(@dieuphoichuyenxe<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and dieuphoichuyenxe = ' + @dieuphoichuyenxe
				else
					begin
						set @query = @query + ' where dieuphoichuyenxe = ' + @dieuphoichuyenxe
						set @flag = 1
					end
			end
			
		if(@quanlydaily<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlydaily = ' + @quanlydaily
				else
					begin
						set @query = @query + ' where quanlydaily = ' + @quanlydaily
						set @flag = 1
					end
			end
		
		if(@quanlydatve<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlydatve = ' + @quanlydatve
				else
					begin
						set @query = @query + ' where quanlydatve = ' + @quanlydatve
						set @flag = 1
					end
			end
		
		if(@quanlykhachhang<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlykhachhang = ' + @quanlykhachhang
				else
					begin
						set @query = @query + ' where quanlykhachhang = ' + @quanlykhachhang
						set @flag = 1
					end
			end
			
		if(@tinhchiphi<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and tinhchiphi = ' + @tinhchiphi
				else
					begin
						set @query = @query + ' where tinhchiphi = ' + @tinhchiphi
						set @flag = 1
					end
			end
			
		if(@thongkenhanvien<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and thongkenhanvien = ' + @thongkenhanvien
				else
					begin
						set @query = @query + ' where thongkenhanvien = ' + @thongkenhanvien
						set @flag = 1
					end
			end
			
		if(@thongkedoanhthu<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and thongkedoanhthu = ' + @thongkedoanhthu
				else
					begin
						set @query = @query + ' where thongkedoanhthu = ' + @thongkedoanhthu
						set @flag = 1
					end
			end
			
		if(@baocaodoanhthu<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and baocaodoanhthu = ' + @baocaodoanhthu
				else
					begin
						set @query = @query + ' where baocaodoanhthu = ' + @baocaodoanhthu
						set @flag = 1
					end
			end
			
		if(@quanlyhoadon<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlyhoadon = ' + @quanlyhoadon
				else
					begin
						set @query = @query + ' where quanlyhoadon = ' + @quanlyhoadon
						set @flag = 1
					end
			end
			
		if(@quanlykhohang<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlykhohang = ' + @quanlykhohang
				else
					begin
						set @query = @query + ' where quanlykhohang = ' + @quanlykhohang
						set @flag = 1
					end
			end
			
		if(@quanlylichtrinh<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlylichtrinh = ' + @quanlylichtrinh
				else
					begin
						set @query = @query + ' where quanlylichtrinh = ' + @quanlylichtrinh
						set @flag = 1
					end
			end
			
		if(@quanlynguoidung<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlynguoidung = ' + @quanlynguoidung
				else
					begin
						set @query = @query + ' where quanlynguoidung = ' + @quanlynguoidung
						set @flag = 1
					end
			end
			
		if(@quanlynhanvien<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlynhanvien = ' + @quanlynhanvien
				else
					begin
						set @query = @query + ' where quanlynhanvien = ' + @quanlynhanvien
						set @flag = 1
					end
			end
			
		if(@quanlytuyenxe<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlytuyenxe = ' + @quanlytuyenxe
				else
					begin
						set @query = @query + ' where quanlytuyenxe = ' + @quanlytuyenxe
						set @flag = 1
					end
			end
		
		if(@quanlyxe<>'')
			begin
				if(@flag = 1)
					set @query = @query + ' and quanlyxe = ' + @quanlyxe
				else
					begin
						set @query = @query + ' where quanlyxe = ' + @quanlyxe
						set @flag = 1
					end
			end
		print(@query)
		exec (@query)
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuPhanQuyen '', '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , ''

if object_id('sp_ThemPhanQuyen', 'p') is not null
drop proc sp_ThemPhanQuyen
go
create proc sp_ThemPhanQuyen
	@maphanquyen int, @capnhattaikhoan int, @dangnhap int, @dangxuat int, @tracuu int, @thaotacdatve int,
    @capnhatthongtinve int, @thaotachuyve int, @dieuphoichuyenxe int, @quanlydaily int, @quanlydatve int,
    @quanlykhachhang int, @tinhchiphi int, @thongkenhanvien int, @thongkedoanhthu int, @baocaodoanhthu int,
    @quanlyhoadon int, @quanlykhohang int, @quanlylichtrinh int, @quanlynguoidung int, @quanlynhanvien int,
    @quanlytuyenxe int, @quanlyxe int
as
begin
	if (@maphanquyen = 0)
        insert into phanquyen
        values (@capnhattaikhoan , @dangnhap , @dangxuat , @tracuu , @thaotacdatve ,
        @capnhatthongtinve , @thaotachuyve , @dieuphoichuyenxe , @quanlydaily , @quanlydatve ,
        @quanlykhachhang , @tinhchiphi , @thongkenhanvien , @thongkedoanhthu , @baocaodoanhthu ,
        @quanlyhoadon , @quanlykhohang , @quanlylichtrinh , @quanlynguoidung , @quanlynhanvien ,
        @quanlytuyenxe , @quanlyxe)
    else
		update phanquyen
		set capnhattaikhoan = @capnhattaikhoan, 
			dangnhap = @dangnhap,
			dangxuat = @dangxuat, 
			tracuu = @tracuu, 
			thaotacdatve = @thaotacdatve,
			capnhatthongtinve = @capnhatthongtinve,
			thaotachuyve = @thaotachuyve,
			dieuphoichuyenxe = @dieuphoichuyenxe,
			quanlydaily = @quanlydaily,
			quanlydatve = @quanlydatve,
			quanlykhachhang = @quanlykhachhang,
			tinhchiphi = @tinhchiphi,
			thongkenhanvien = @thongkenhanvien,
			thongkedoanhthu = @thongkedoanhthu,
			baocaodoanhthu = @baocaodoanhthu,
			quanlyhoadon = @quanlyhoadon,
			quanlykhohang = @quanlykhohang,
			quanlylichtrinh = @quanlylichtrinh,
			quanlynguoidung = @quanlynguoidung,
			quanlynhanvien = @quanlynhanvien,
			quanlytuyenxe = @quanlytuyenxe,
			quanlyxe = @quanlyxe
		where maphanquyen = @maphanquyen
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_ThemPhanQuyen 2, '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '0'

if object_id('sp_XoaPhanQuyen', 'p') is not null
drop proc sp_XoaPhanQuyen
go
create proc sp_XoaPhanQuyen
        @maphanquyen int
as
begin
        delete from phanquyen
        where maphanquyen = @maphanquyen
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end

if object_id('sp_DanhSachDaiLyTheoKhuVuc', 'p') is not null
drop proc sp_DanhSachDaiLyTheoKhuVuc
go
create proc sp_DanhSachDaiLyTheoKhuVuc
        @khuvuc nvarchar(50)
as
begin
		select * from daily
		where khuvuc = N'' + @khuvuc + ''		       
        if(@@error <> 0)begin
                rollback tran
                return
        end
end

exec sp_DanhSachDaiLyTheoKhuVuc N'Miền Bắc'

if object_id('sp_DanhSachKhuVuc', 'p') is not null
drop proc sp_DanhSachKhuVuc
go
create proc sp_DanhSachKhuVuc
as
begin
		select distinct khuvuc from daily
        if(@@error <> 0)begin
                rollback tran
                return
        end
end

exec sp_DanhSachKhuVuc

if object_id('sp_DanhSachDaiLyTheoLichTrinh', 'p') is not null
drop proc sp_DanhSachDaiLyTheoLichTrinh
go
create proc sp_DanhSachDaiLyTheoLichTrinh
	@malichtrinh int
as
begin
		select daily.madaily, daily.tendaily, daily.diachi, daily.sodt, daily.khuvuc, dailylichtrinh.thoigianqua from daily, dailylichtrinh
		where dailylichtrinh.malichtrinh = @malichtrinh
		and daily.madaily = dailylichtrinh.madaily
        if(@@error <> 0)begin
                rollback tran
                return
        end
end

exec sp_DanhSachDaiLyTheoLichTrinh 2

if object_id('sp_TraCuuXeTheoMa', 'p') is not null
drop proc sp_TraCuuXeTheoMa
go
create proc sp_TraCuuXeTheoMa
	@maxe int
as
begin
		select * from xe
		where maxe = @maxe
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuXeTheoMa 1

----HoaDonDatVe
if object_id('sp_TraCuuHoaDonTheoNgay', 'p') is not null
drop proc sp_TraCuuHoaDonTheoNgay
go
create proc sp_TraCuuHoaDonTheoNgay
	@ngaylap nvarchar(50)
as
begin
		select mahoadon from hoadondatve
		where ngaylap = @ngaylap
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuHoaDonTheoNgay '2012/09/13'

----DatVe
if object_id('sp_TraCuuDatVeTheoMaHoaDon', 'p') is not null
drop proc sp_TraCuuDatVeTheoMaHoaDon
go
create proc sp_TraCuuDatVeTheoMaHoaDon
	@mahoadon int
as
begin
		select * from datve
		where datve.mahoadon = @mahoadon
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuDatVeTheoMaHoaDon 1
----
if object_id('sp_ThemDatVe', 'p') is not null
drop proc sp_ThemDatVe
go
create proc sp_ThemDatVe
	@madatve int, @mahoadon int, @mahang int, @maloaive int, @makh int, @manv int, @machuyen int,
	@ngaydatve datetime, @ngaydi datetime, @maghe int, @maxe int, @tinhtrangdatve int, @thanhtien int
as
begin
	if (@madatve = '')
        insert into datve
        values (@mahoadon, @mahang, @maloaive, @makh, @manv, @machuyen,
        @ngaydatve, @ngaydi, @maghe, @maxe, @tinhtrangdatve, @thanhtien)
    else
		update datve
		set mahoadon = @mahoadon,
		mahang = @mahang,
		maloaive = @maloaive,
		makh = @makh,
		manv = @manv,
		machuyen = @machuyen,
		ngaydatve = @ngaydatve,
		ngaydi = @ngaydi,
		maghe = @maghe,
		maxe = @maxe,
		tinhtrangdatve = @tinhtrangdatve,
		thanhtien = @thanhtien
		where madatve = @madatve
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_ThemDatVe 5, 1, 1, 1, 1, 1, 1, '2012/05/17', '2012/05/17', 1, 1, 1, 3000000
----KhachHang
if object_id('sp_TraCuuKhachHangTheoMaDatVe', 'p') is not null
drop proc sp_TraCuuKhachHangTheoMaDatVe
go
create proc sp_TraCuuKhachHangTheoMaDatVe
	@madatve int
as
begin
		select khachhang.* from datve, khachhang
		where datve.madatve = @madatve
		and datve.makh = khachhang.makh
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuKhachHangTheoMaDatVe 2
----
if object_id('sp_TraCuuKhachHang', 'p') is not null
drop proc sp_TraCuuKhachHang
go
create proc sp_TraCuuKhachHang
as
begin
		select * from khachhang
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuKhachHang
----
if object_id('sp_TraCuuKhachHangTheoMaKhachHang', 'p') is not null
drop proc sp_TraCuuKhachHangTheoMaKhachHang
go
create proc sp_TraCuuKhachHangTheoMaKhachHang
	@makh int
as
begin
		select * from khachhang
		where makh = @makh
		if(@@error <> 0)begin
			rollback tran
			return
        end
end 
exec sp_TraCuuKhachHangTheoMaKhachHang 1
----End KhachHang
----KhoHang
if object_id('sp_TraKhoHangTheoMaHang', 'p') is not null
drop proc sp_TraKhoHangTheoMaHang
go
create proc sp_TraKhoHangTheoMaHang
	@mahang int
as
begin
		select khohang.* from khohang, hang
		where hang.mahang = @mahang
		and hang.makho = khohang.makho
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraKhoHangTheoMaHang 2
----
if object_id('sp_TraCuuKhoHang', 'p') is not null
drop proc sp_TraCuuKhoHang
go
create proc sp_TraCuuKhoHang
as
begin
		select * from khohang
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuKhoHang
----
if object_id('sp_TraMaKhoHangTheoMaHang', 'p') is not null
drop proc sp_TraMaKhoHangTheoMaHang
go
create proc sp_TraMaKhoHangTheoMaHang
	@mahang int
as
begin
		select khohang.makho from khohang, hang
		where hang.mahang = @mahang
		and hang.makho = khohang.makho
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraMaKhoHangTheoMaHang 2
----End KhoHang
----Chuyen
if object_id('sp_TraCuuChuyenTheoMaChuyen', 'p') is not null
drop proc sp_TraCuuChuyenTheoMaChuyen
go
create proc sp_TraCuuChuyenTheoMaChuyen
	@machuyen int
as
begin
		select * from chuyen
		where machuyen = @machuyen
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuChuyenTheoMaChuyen 2
----
if object_id('sp_TraCuuChuyenTheoMaTuyen', 'p') is not null
drop proc sp_TraCuuChuyenTheoMaTuyen
go
create proc sp_TraCuuChuyenTheoMaTuyen
	@matuyen int
as
begin
		select * from chuyen
		where matuyen = @matuyen
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuChuyenTheoMaTuyen 3
----
if object_id('sp_TraCuuChuyen', 'p') is not null
drop proc sp_TraCuuChuyen
go
create proc sp_TraCuuChuyen
as
begin
		select * from chuyen
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuChuyen
----End Chuyen
----Tuyen
if object_id('sp_TraCuuTuyenTheoMaChuyen', 'p') is not null
drop proc sp_TraCuuTuyenTheoMaChuyen
go
create proc sp_TraCuuTuyenTheoMaChuyen
	@machuyen int
as
begin
		select tuyen.* from chuyen, tuyen
		where chuyen.machuyen = @machuyen
		and chuyen.matuyen = tuyen.matuyen
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuTuyenTheoMaChuyen 1
----
if object_id('sp_TraCuuTuyen', 'p') is not null
drop proc sp_TraCuuTuyen
go
create proc sp_TraCuuTuyen
as
begin
		select * from tuyen
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuTuyen
----End Tuyen
----Hang
if object_id('sp_TraCuuHangTheoMaDatVe', 'p') is not null
drop proc sp_TraCuuHangTheoMaDatVe
go
create proc sp_TraCuuHangTheoMaDatVe
	@madatve int
as
begin
		select hang.* from hang, datve
		where datve.madatve = @madatve
		and datve.mahang = hang.mahang
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuHangTheoMaDatVe 1
----
if object_id('sp_TraCuuHang', 'p') is not null
drop proc sp_TraCuuHang
go
create proc sp_TraCuuHang
as
begin
		select * from hang
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuHang
----
if object_id('sp_TraCuuHangTheoMaHang', 'p') is not null
drop proc sp_TraCuuHangTheoMaHang
go
create proc sp_TraCuuHangTheoMaHang
	@mahang int
as
begin
		select * from hang
		where mahang = @mahang
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuHangTheoMaHang 1
----End Hang

----HoaDonDatVe
if object_id('sp_ThemHoaDonDatVe', 'p') is not null
drop proc sp_ThemHoaDonDatVe
go
create proc sp_ThemHoaDonDatVe
	@mahoadon int, @manv int, @ngaylap datetime, @tongtien int, @madaily int, @tinhtrang int
as
begin
	if (@mahoadon = '')
        insert into hoadondatve
        values (@manv, @ngaylap, @tongtien, @madaily, @tinhtrang)
    else
		update hoadondatve
		set manv = @manv,
			ngaylap = @ngaylap,
			tongtien = @tongtien,
			madaily = @madaily,
			tinhtrang = @tinhtrang
		where mahoadon = @mahoadon
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_ThemHoaDonDatVe '5','4', '2012/05/17', 47000000, 1, 1
----End HoaDonDatVe
----LoaiVe
if object_id('sp_TraCuuDonGiaTheoMaLoaiVe', 'p') is not null
drop proc sp_TraCuuDonGiaTheoMaLoaiVe
go
create proc sp_TraCuuDonGiaTheoMaLoaiVe
	@maloaive int
as
begin
		select dongia from loaive
		where maloaive = @maloaive
		if(@@error <> 0)begin
			rollback tran
			return
        end
end
exec sp_TraCuuDonGiaTheoMaLoaiVe 1
----End LoaiVe
----DaiLyLichTrinh
if object_id('sp_ThemDaiLyLichTrinh', 'p') is not null
drop proc sp_ThemDaiLyLichTrinh
go
create proc sp_ThemDaiLyLichTrinh
	@malichtrinh int, @madaily int, @thoigianqua datetime, @action int
as
begin
	if(@action = 0)
		begin
			insert into dailylichtrinh
			values(@malichtrinh, @madaily, @thoigianqua)
		end
	else
		begin
			update dailylichtrinh
			set thoigianqua = @thoigianqua
			where malichtrinh = @malichtrinh 
			and madaily = @madaily
		end
    if(@@error <> 0)
	begin
		rollback tran
		return
    end
end
----End DaiLyLichTrinh